﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ScannerApp.DataBase;
using System.Data;
using System.Runtime.InteropServices;
using System.Data.SqlClient;
using System.Net;
using Newtonsoft.Json;
using XHD.Controller;
using XHD.Model;
using XHD.DBUtility;
using XHD.Common;

namespace ScannerApp.DataBase
{
    public abstract class ScanAppSQLLogic
    {
      private static  XHD.BLL.Sys_Param sp = new XHD.BLL.Sys_Param();
        /// <summary>
        /// 扫码登录
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>

        public static Tuple<bool, string, DataTable> GetDatable_hr_employee(string barcode, string mac)
        {
            DataTable ds = new DataTable();

            var sb = new System.Text.StringBuilder();
            sb.AppendLine("SELECT * FROM dbo.hr_employee WHERE (id = '" + barcode + "' OR idcard='" + barcode+"')");
            var log = new XHD.BLL.Sys_log();
            var modellog = new XHD.Model.Sys_log();
            modellog.EventType = "系统登录";
            modellog.id = Guid.NewGuid().ToString().ToUpper();
            modellog.EventDate = DateTime.Now;
            modellog.UserID = barcode;
            modellog.IPStreet = mac;

            log.Add(modellog);

            try
            {
                ds = SqlHelper.ExecuteDataTableText(sb.ToString());
            }
            catch (Exception exception)
            {
                return Tuple.Create<bool, string, DataTable>(false, exception.Message, ds);
            }
            finally
            {

            }
            return Tuple.Create<bool, string, DataTable>(true, string.Empty, ds);
        }
        /// <summary>
        /// 获取单据
        /// </summary>
        /// <param name="barcode"></param>
        /// <returns></returns>
        public static Tuple<bool, string, DataTable> GetDataTable_boar_collection(string barcode, string status)
        {
            var sb = new System.Text.StringBuilder();
            sb.AppendLine(" SELECT A.*,B.id,B.Serialnumber,mx.product_id AS mxID,B.Customer_id,E.cus_name,C.product_name DeviceName,D.product_name BoarName  ");
            sb.AppendLine(" , (select params_name from Sys_Param where id = A.collectStation) as collectStationName  ");
            sb.AppendLine("  , (select params_name from Sys_Param where id = A.WorkstationNo) as WorkstationName  ");
            sb.AppendLine(" ,mx.agio,mx.quantity,p.product_name,p.unit,p.specifications");
            sb.AppendLine("  ,(SELECT sys_value FROM  dbo.Sys_info WHERE sys_key='sys_name') companyName");
            sb.AppendLine("   FROM dbo.boar_collection A  ");
            sb.AppendLine(" INNER JOIN  dbo.Sale_order_details mx ON	 A.orderid=mx.order_id  AND mx.product_id=A.ordermxid");
            sb.AppendLine("   INNER JOIN  dbo.Product p ON A.ordermxid = p.id ");
            sb.AppendLine(" INNER JOIN  dbo.Sale_order B ON	 mx.order_id=B.id  ");
            sb.AppendLine(" INNER JOIN dbo.Basic_device C ON	A.deviceid=C.id  ");
            sb.AppendLine(" INNER JOIN dbo.Basic_boar D ON	 A.boarid=D.id  ");
            sb.AppendLine(" INNER JOIN  dbo.CRM_Customer E ON	 B.Customer_id=E.id  ");
            sb.AppendLine(" where   ISNULL(A.IsStatus,'0') IN(" + status + ")");
            sb.AppendLine(" and A.id='" + barcode + "'");
            DataTable lsds =  new DataTable();

            try
            {
                lsds = SqlHelper.ExecuteDataTableText(sb.ToString());
            }
            catch (Exception exception)
            {
                return Tuple.Create<bool, string, DataTable>(false, exception.Message, lsds);
            }
            finally
            {

            }
            return Tuple.Create<bool, string, DataTable>(true, string.Empty, lsds);
        }
        /// <summary>
        /// 单据手工退回
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static Tuple<bool, string> Update_boar_collection_back(string id, string status)
        {
            var sb = new System.Text.StringBuilder();
            sb.AppendLine("UPDATE boar_collection");
            sb.AppendLine(" SET   IsStatus = '" + status + "' ");
            sb.AppendLine("  WHERE id= '" + id + "' ");
            sb.AppendLine("");
            try
            {
                if (SqlHelper.ExecteNonQueryText(sb.ToString()) > 0)
                    return Tuple.Create<bool, string>(true, string.Empty);
                else return Tuple.Create<bool, string>(false, "更新失败！");

            }
            catch (Exception exception)
            {
                return Tuple.Create<bool, string>(false, exception.Message);
            }
            finally
            {

            }
            return Tuple.Create<bool, string>(true, string.Empty);
        }



        /// <summary>
        /// 称重提交
        /// </summary>
        /// <param name="weights"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public static Tuple<bool, string> Update_boar_collection_Weights(string weights, string id)
        {
            var sb = new System.Text.StringBuilder();
            sb.AppendLine("UPDATE boar_collection");
            sb.AppendLine(" SET weights = '" + weights + "',weightTime=getdate(), IsStatus = '40' ");
            sb.AppendLine("  WHERE id= '" + id + "' ");
            sb.AppendLine("");
            try
            {
                if (SqlHelper.ExecteNonQueryText(sb.ToString()) > 0)
                    return Tuple.Create<bool, string>(true, string.Empty);
                else return Tuple.Create<bool, string>(false, "更新失败！");

            }
            catch (Exception exception)
            {
                return Tuple.Create<bool, string>(false, exception.Message);
            }
            finally
            {

            }
            return Tuple.Create<bool, string>(true, string.Empty);
        }


        public static Tuple<bool, string, DataTable> GetList_boar_collection(string DevID, string type)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine(" SELECT TOP 10 A.EventType,A.EventTitle,A.Log_Content,*,C.product_name AS DeviceName FROM dbo.Sys_log A  ");
            sb.AppendLine(" INNER JOIN  boar_collection B ON B.id=A.EventTitle  ");
            sb.AppendLine(" LEFT JOIN  dbo.Basic_device C ON  C.id=A.EventID  ");

            sb.AppendLine(" WHERE 1=1  ");
            if (type == "collection")
            {
                sb.AppendLine(" AND  B.boarid='" + DevID + "'");
            }
            else
                sb.AppendLine(" AND A.EventTitle='" + DevID + "'");
            //if (type == "Weights")
            //{ sb.AppendLine(" AND  IsStatus = '30' "); }
            //else if (type == "md")//密度保存
            //{ sb.AppendLine(" AND  IsStatus = '50' "); }
            //else if (type == "hl")//活力保存
            //{ sb.AppendLine(" AND  IsStatus = '70' "); }
            //else if (type == "xs")//稀释保存
            //{ sb.AppendLine(" AND  IsStatus = '90' "); }  

            sb.AppendLine(" 	 ORDER BY A.EventDate DESC  ");

            DataTable lsds = new DataTable();
            try
            {
                lsds = SqlHelper.ExecuteDataTableText(sb.ToString());
            }
            catch (Exception exception)
            {
                return Tuple.Create<bool, string, DataTable>(false, exception.Message, lsds);
            }
            finally
            {

            }
            return Tuple.Create<bool, string, DataTable>(true, string.Empty, lsds);
        }


        public static Tuple<bool, string, DataTable> GetListBasic_boar(string barcode)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine(" SELECT A.id, A.product_name,A.unit,A.status,A.barcode,A.Drug_id,A.LastEfficaTime,A.LastCollecTime,ISNULL(img_url,'')img_url  ");
            sb.AppendLine("  ,A.shortcode ");//简称
            sb.AppendLine(" ,A.EfficacyState,A.CollectionStatus,DATEDIFF(DAY, birthday,GETDATE()) nlday");
            sb.AppendLine(" ,B.*,dorm.dormName  ");
            sb.AppendLine(" ,C.product_name AS cname   ");
            sb.AppendLine(" ,D.product_category,corral.corralName  ");
            sb.AppendLine("  FROM  dbo.Basic_boar  A  ");
            sb.AppendLine("   LEFT JOIN  dbo.Basic_Drug B ON	A.Drug_id=B.IDCode  ");
            sb.AppendLine("   LEFT JOIN  dbo.Product C ON	A.category_id=C.id  ");
            sb.AppendLine("   LEFT JOIN  dbo.Product_category D ON	C.category_id=D.id  ");
            sb.AppendLine("  LEFT JOIN  Basic_corral corral ON A.corralId=corral.id ");
           sb.AppendLine("    LEFT JOIN Basic_dorm dorm ON A.dormId=dorm.id	");
            sb.AppendLine(" where 1=1");
            if (barcode != "")
                sb.AppendLine(" and (A.barcode='" + barcode + "' OR A.shortcode='"+barcode+"')");
            DataTable lsds = null;
            try
            {
                lsds = SqlHelper.ExecuteDataTableText(sb.ToString());
            }
            catch (Exception exception)
            {
                return Tuple.Create<bool, string, DataTable>(false, exception.Message, lsds);
            }
            finally
            {

            }
            return Tuple.Create<bool, string, DataTable>(true, string.Empty, lsds);
        }

        public static Tuple<bool, string, DataTable> GetListboar_collection(string barcode)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("   ");
            sb.AppendLine(" SELECT A.id,A.collectDate,A.IsStatus  ");
            sb.AppendLine(" ,B.capacity,B.quantity    ");
            sb.AppendLine(" ,C.Serialnumber,C.Order_date  ");
            sb.AppendLine(" ,D.barcode  ");
            sb.AppendLine(" ,E.StatusName  ");
            sb.AppendLine(" FROM  dbo.boar_collection A  ");
            sb.AppendLine(" LEFT JOIN  dbo.Sale_order_details B ON	A.ordermxid=B.product_id AND	 A.orderid=B.order_id  ");
            sb.AppendLine(" LEFT JOIN  dbo.Sale_order C ON	A.orderid=C.id  ");
            sb.AppendLine(" INNER JOIN dbo.Basic_boar D ON	A.boarid=D.id  ");
            sb.AppendLine(" INNER JOIN  dbo.DocStatus_Name E ON	A.IsStatus=E.IsStatusCode  ");
            sb.AppendLine(" WHERE	 A.IsStatus NOT in ('NN' ,'YY')  ");
            sb.AppendLine(" AND  A.IsStatus in ('10' ) ");//增加，必须要是待采集

            if (barcode != "")
                sb.AppendLine(" and (D.barcode='" + barcode + "'OR D.shortcode='" + barcode + "')");
            DataTable lsds = new DataTable();
            try
            {
                lsds = SqlHelper.ExecuteDataTableText(sb.ToString());
            }
            catch (Exception exception)
            {
                return Tuple.Create<bool, string, DataTable>(false, exception.Message, lsds);
            }
            finally
            {

            }
            return Tuple.Create<bool, string, DataTable>(true, string.Empty, lsds);
        }


        public static Tuple<bool, string> Sys_log(string paras)
        {
            var sb = new System.Text.StringBuilder();
            object obj = JsonConvert.DeserializeObject("[" + paras + "]");//obj   转换json格式的字符串为obj对象
            Newtonsoft.Json.Linq.JArray js = obj as Newtonsoft.Json.Linq.JArray;
            foreach (Newtonsoft.Json.Linq.JObject ja in js) //把上面的obj转换为 Jobject对象
            {
                sb.AppendLine(" INSERT INTO dbo.Sys_log  ");
                sb.AppendLine("         ( id ,  ");
                sb.AppendLine("           EventType ,  ");
                sb.AppendLine("           EventID ,  ");
                sb.AppendLine("           EventTitle ,  ");
                sb.AppendLine("           Log_Content ,  ");
                sb.AppendLine("           UserID ,  ");
                sb.AppendLine("           UserName ,  ");
                sb.AppendLine("           IPStreet ,  ");
                sb.AppendLine("           EventDate  ");
                sb.AppendLine("         )  ");
                sb.AppendLine(" VALUES  ( NEWID() ,  ");
                sb.AppendLine("           '" + ja["EventType"].ToString() + "' , ");
                sb.AppendLine("           '" + ja["EventID"].ToString() + "' , ");
                sb.AppendLine("            '" + ja["EventTitle"].ToString() + "' ,  ");
                sb.AppendLine("           '" + ja["Log_Content"].ToString() + "'  , ");
                sb.AppendLine("            '" + ja["UserID"].ToString() + "'  ,  ");
                sb.AppendLine("            '" + ja["UserName"].ToString() + "'  ,   ");
                sb.AppendLine("           '" + GetAddressIP() + "' ,   ");
                sb.AppendLine("           GETDATE()    ");
                sb.AppendLine("         )  ");
            }

            try
            {
                SqlHelper.ExecteNonQueryText(sb.ToString());

            }
            catch (Exception exception)
            {
                return Tuple.Create<bool, string>(false, exception.Message);
            }
            finally
            {

            }
            return Tuple.Create<bool, string>(true, string.Empty);
        }

        public static Tuple<bool, string> PC_DeviceName(string type, string paras)
        {
            StringBuilder sb = new StringBuilder();
            string msg = "";
            object obj = JsonConvert.DeserializeObject("[" + paras + "]");//obj   转换json格式的字符串为obj对象
            Newtonsoft.Json.Linq.JArray js = obj as Newtonsoft.Json.Linq.JArray;
            foreach (Newtonsoft.Json.Linq.JObject ja in js) //把上面的obj转换为 Jobject对象
            {
                string sql = " SELECT A.*,B.product_name FROM PC_DeviceName	A " +
                        "  INNER JOIN  dbo.Basic_device B ON A.DeviceName = B.id WHERE MacAddress =  '" + ja["MacAddress"].ToString() + "' OR DeviceName =  '" + ja["DeviceName"].ToString() + "'";
                DataTable dt = SqlHelper.ExecuteDataTableText(sql);
                if (dt.Rows.Count > 0) { msg = "此电脑或此编号已经绑定过！"; }
                if (type == "ADD")
                {
                    sb.AppendLine(" INSERT INTO dbo.PC_DeviceName  ");
                    sb.AppendLine("         ( ID ,  ");
                    sb.AppendLine("           DeviceName ,  ");
                    sb.AppendLine("           CreateTime ,  ");
                    sb.AppendLine("           MacAddress ,  ");
                    sb.AppendLine("           IsStop  ");
                    sb.AppendLine("         )  ");
                    sb.AppendLine(" VALUES  ( NEWID()  , -- ID - varchar(50)  ");
                    sb.AppendLine("          '" + ja["DeviceName"].ToString() + "'   , -- DeviceName - varchar(50)  ");
                    sb.AppendLine("           GETDATE() , -- CreateTime - datetime  ");
                    sb.AppendLine("          '" + ja["MacAddress"].ToString() + "' , -- MacAddress - varchar(50)  ");
                    sb.AppendLine("           0  -- IsStop - bit  ");
                    sb.AppendLine("         )  ");
                }

            }

            try
            {
                if (msg == "")
                {
                    if (type == "ADD")
                    {
                        if (SqlHelper.ExecteNonQueryText(sb.ToString()) <= 0)
                            return Tuple.Create<bool, string>(false, "绑定失败！联系管理员!");
                    }

                }
                else
                {
                    return Tuple.Create<bool, string>(false, msg);
                }

            }
            catch (Exception exception)
            {
                return Tuple.Create<bool, string>(false, exception.Message);
            }
            finally
            {

            }
            return Tuple.Create<bool, string>(true, string.Empty);
        }

        public static Tuple<bool, string, DataTable> GetLogin(string paras)
        {
            StringBuilder sb = new StringBuilder();
            string msg = "";
            object obj = JsonConvert.DeserializeObject("[" + paras + "]");//obj   转换json格式的字符串为obj对象
            Newtonsoft.Json.Linq.JArray js = obj as Newtonsoft.Json.Linq.JArray;
            DataTable lsdt = new DataTable();
            foreach (Newtonsoft.Json.Linq.JObject ja in js) //把上面的obj转换为 Jobject对象
            {
                sb.AppendLine("    SELECT A.*,B.product_name,C.EventDate,hr.name   ");
                sb.AppendLine("    ,(SELECT sys_value FROM dbo.Sys_info WHERE sys_key='sys_name')sys_name  ");
                sb.AppendLine("    ,(SELECT sys_value FROM dbo.Sys_info WHERE sys_key='sys_logo')sys_logo  ");
                sb.AppendLine("    FROM PC_DeviceName	A       ");
                sb.AppendLine("         INNER JOIN  dbo.Basic_device B ON A.DeviceName = B.id     ");
                sb.AppendLine("                  left JOIN (SELECT TOP	1 * FROM	 dbo.Sys_log WHERE IPStreet='" + ja["MacAddress"].ToString() + "' ORDER BY EventDate DESC	)C ON	A.MacAddress=C.IPStreet     ");
                sb.AppendLine("                  LEFT JOIN dbo.hr_employee hr ON	C.UserID=hr.id     ");
                sb.AppendLine("    ");

                sb.AppendLine("  WHERE A.MacAddress =  '" + ja["MacAddress"].ToString() + "' OR A.DeviceName =  '" + ja["DeviceName"].ToString() + "' ");

                if (lsdt.Rows.Count <= 0) { msg = "此电脑还未绑定！"; }

            }

            try
            {
                lsdt = SqlHelper.ExecuteDataTableText(sb.ToString());
                ExecUpdateStatusJob();
                if (msg == "")
                {

                    return Tuple.Create<bool, string, DataTable>(true, msg, lsdt);
                }
                else return Tuple.Create<bool, string, DataTable>(false, msg, lsdt);

            }
            catch (Exception exception)
            {
                return Tuple.Create<bool, string, DataTable>(false, exception.Message, lsdt);
            }
            finally
            {

            }
            return Tuple.Create<bool, string, DataTable>(true, string.Empty, lsdt);
        }

        public static Tuple<bool, string> UpdateIsStart(string yesorno)
        {
            var sb = new System.Text.StringBuilder();


            sb.AppendLine("UPDATE A set IsStart='" + yesorno + "' ");
            sb.AppendLine("FROM dbo.IsStart  A ");


            try
            {
                SqlHelper.ExecteNonQueryText(sb.ToString());
            }
            catch (Exception exception)
            {
                return Tuple.Create<bool, string>(false, exception.Message);
            }
            finally
            {

            }
            return Tuple.Create<bool, string>(true, string.Empty);
        }

        public static Tuple<bool, string, DataTable> GetList_Basic_Device()
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine(" SELECT *,status FROM  dbo.Basic_device  ");
            sb.AppendLine("  WHERE id NOT IN  (SELECT DeviceName FROM dbo.PC_DeviceName WHERE IsStop=0)  ");
            sb.AppendLine("      ");

            DataTable lsds = null;
            try
            {
                lsds = SqlHelper.ExecuteDataTableText(sb.ToString());
            }
            catch (Exception exception)
            {
                return Tuple.Create<bool, string, DataTable>(false, exception.Message, lsds);
            }
            finally
            {

            }
            return Tuple.Create<bool, string, DataTable>(true, string.Empty, lsds);
        }

        public static Tuple<bool, string> Save_boar_collection(string type, string collector, string deviceid, string boarid, string collectStation, string WorkstationNo, string doperson)
        {
            //混合的流程应该是，1、先判断扫的耳标是否存在状态为10的作业单，如存在作业单，并CollectionStatus=1就对应此作业单进行作来。
           // 2、如无作业单，则判断status = Y并CollectionStatus = 1生成新订单新作业单后，并将这两个状态改为N和1
            string flag = "";string newboarid = "";string errmsg = "";
            if ((type == "MixWorkNoOrder") || type == "DirectWork")
            {
                try
                {
                    XHD.BLL.Sys_Param sp = new XHD.BLL.Sys_Param();
                    XHD.BLL.Basic_boar BB = new XHD.BLL.Basic_boar();
                    DataSet ds = BB.GetList(" (shortcode='" + boarid + "'OR barcode='" + boarid + "')");
                    //如果查询不到种源，直接生成新的
                    bool isnew = false;bool IsCanUse = true;
                    if (ds == null) { isnew = true; }
                    else
                    {
                        if (ds.Tables[0].Rows.Count <= 0) { isnew = true; }
                        else {
                            if (ds.Tables[0].Rows[0]["shortcode"].ToString().Length < 8)//要求短号必须大于等于8位，否则系统重新生成
                            {

                                string shortcode = sp.GetBarCode("SHORT");
                                string sqlshort = "UPDATE Basic_boar SET	 shortcode='" + shortcode + "' WHERE barcode='" + boarid + "' and isnull(shortcode,'')=''";
                                DbHelperSQL.ExecuteSql(sqlshort);
                            }
                            newboarid = ds.Tables[0].Rows[0]["id"].ToString();
                            //增加，种源不可用状态
                            if (ds.Tables[0].Rows[0]["status"].ToString() == "N")
                            {
                                errmsg = "种源状态在途！";
                                   IsCanUse = false;
                            }
                            if (ds.Tables[0].Rows[0]["CollectionStatus"].ToString() != "1")
                            {
                                errmsg = "种源状态已采集！";
                                IsCanUse = false;
                            }
                        }
                    }
                    
                    if (isnew) {
                        newboarid = Guid.NewGuid().ToString();
                        CreateNewBoar(collector, boarid, newboarid);
                       // errmsg = "种源不存在！";
                    }
                     
                }
                catch (Exception e) { }
               if(errmsg =="")
                flag = InsertBoarCollection(collector, deviceid, newboarid, collectStation, WorkstationNo, doperson);
            }//先判断
            else {
                flag = "ok";
            } 
            if(flag!="")
            return Tuple.Create<bool, string>(true, flag);
            else return Tuple.Create<bool, string>(false, "失败！"+ errmsg);
        }
        public static Tuple<bool, string> IsExist_collection(   string  barcode)
        { 
            try
            {
                string sql = "SELECT count(*) sl  FROM dbo.boar_collection A  " +
                    " INNER JOIN  dbo.Basic_boar B ON A.boarid = B.id "+
                     " WHERE(B.barcode = '"+ barcode + "' OR B.shortcode = '" + barcode + "') AND IsStatus = '10'";
             bool r=   DbHelperSQL.Exists(sql);
            if (r)
                {
                    return Tuple.Create<bool, string>(true, "");
                }
            else return Tuple.Create(false, "不存在");
            }
            catch (Exception exception)
            {
                return Tuple.Create<bool, string>(false, exception.Message);
            }
            finally
            {

            }
            return Tuple.Create<bool, string>(true, string.Empty);
        }
        /// <summary>
        /// 称重提交
        /// </summary>
        /// <param name="weights"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public static Tuple<bool, string> Update_boar_collection(string type, string para, string id)
        {
             
                var sb = new System.Text.StringBuilder();
            sb.AppendLine("UPDATE boar_collection");

            if (type == "collection_save")//采集保存
                sb.AppendLine(" SET  IsStatus = '15' ");
            else if (type == "collection_submit")//采集提交
                sb.AppendLine(" SET  IsStatus = '20' ,collector='" + para + "',collectDate=getdate() ");

            sb.AppendLine("  WHERE id= '" + id + "' ");

            if (type == "collection_save")//采集保存
                sb.AppendLine(" AND IsStatus ='10' ");
            else if (type == "collection_submit")//采集提交
                sb.AppendLine(" AND IsStatus in('10','15') ");


            try
            {
                if (SqlHelper.ExecteNonQueryText(sb.ToString()) > 0)
                {
                    sb.Clear();
                    //一旦提交不能修改，因为采集后不可逆
                    if (type == "collection_submit")//采集提交(暂时采集没有保存，先放这里）
                    {
                        sb.AppendLine("   UPDATE B SET	B.CollectionStatus=3 ,status='N',B.LastCollecTime=getdate()");
                        sb.AppendLine("  FROM dbo.boar_collection A ");
                        sb.AppendLine("  INNER JOIN  dbo.Basic_boar B ON  A.boarid = B.id");
                        sb.AppendLine(" WHERE A.id= '" + id + "'  ");
                        if (SqlHelper.ExecteNonQueryText(sb.ToString()) > 0)
                            return Tuple.Create<bool, string>(true, string.Empty);
                        else return Tuple.Create<bool, string>(false, "种源状态更新失败！");
                    }
                    else Tuple.Create<bool, string>(true, string.Empty);

                }
                else return Tuple.Create<bool, string>(false, "更新失败！");
            }
            catch (Exception exception)
            {
                return Tuple.Create<bool, string>(false, exception.Message);
            }
            finally
            {

            }
            return Tuple.Create<bool, string>(true, string.Empty);
        }

        private static bool CreateNewBoar(string collector,string boarid,string id)
        {
            string shortcode = sp.GetBarCode("SHORT");
            StringBuilder sb = new StringBuilder();
            sb.AppendLine(" INSERT dbo.Basic_boar  ");
            sb.AppendLine(" (  ");
            sb.AppendLine("     id,  ");
            sb.AppendLine("     product_name,  ");
            sb.AppendLine("     category_id,  ");
            sb.AppendLine("     status,  ");
            sb.AppendLine("     unit,  ");
            sb.AppendLine("     cost,  ");
            sb.AppendLine("     price,  ");
            sb.AppendLine("     agio,  ");
            sb.AppendLine("     remarks,  ");
            sb.AppendLine("     specifications,  ");
            sb.AppendLine("     create_id,  ");
            sb.AppendLine("     create_time,  ");
            sb.AppendLine("     barcode,  ");
            sb.AppendLine("     CollectionStatus,  ");
            sb.AppendLine("     EfficacyState,  ");
            sb.AppendLine("     LastCollecTime,  ");
            sb.AppendLine("     LastEfficaTime,  ");
            sb.AppendLine("     Drug_id,  ");
            sb.AppendLine("     img_url,  ");
            sb.AppendLine("     birthday,  ");
            sb.AppendLine("     shortcode  ");
            sb.AppendLine(" )  ");
            sb.AppendLine(" VALUES  ");
            sb.AppendLine(" (   '"+ id + "',        -- id - varchar(50)  ");
            sb.AppendLine("     '自动生成',        -- product_name - varchar(250)  ");
            sb.AppendLine("     '00000000-0000-0000-0000-000000000000',        -- category_id - varchar(50)  ");
            sb.AppendLine("     'Y',        -- status - varchar(5)  ");
            sb.AppendLine("     '头',        -- unit - varchar(250)  ");
            sb.AppendLine("     NULL,      -- cost - decimal(18, 2)  ");
            sb.AppendLine("     NULL,      -- price - decimal(18, 2)  ");
            sb.AppendLine("     NULL,      -- agio - decimal(18, 2)  ");
            sb.AppendLine("     '',        -- remarks - varchar(max)  ");
            sb.AppendLine("     '',        -- specifications - varchar(250)  ");
            sb.AppendLine("     '"+ collector + "',        -- create_id - varchar(50)  ");
            sb.AppendLine("     GETDATE(), -- create_time - datetime  ");
            sb.AppendLine("     '"+ boarid + "',        -- barcode - varchar(50)  ");
            sb.AppendLine("     1,         -- CollectionStatus - smallint  ");
            sb.AppendLine("     1,         -- EfficacyState - smallint  ");
            sb.AppendLine("     Null, -- LastCollecTime - datetime  ");
            sb.AppendLine("     Null, -- LastEfficaTime - datetime  ");
            sb.AppendLine("     '',        -- Drug_id - varchar(50)  ");
            sb.AppendLine("     'images/noheader.png',        -- img_url - varchar(500)  ");
            sb.AppendLine("     GETDATE()-100, -- birthday - datetime  ");
            sb.AppendLine("     '"+ shortcode + "'         -- shortcode - varchar(50)  ");
            sb.AppendLine("     );  ");
            if (DbHelperSQL.ExecuteSql(sb.ToString()) > 0)
            {
                return true;
            }
            else return false;

        }

        /// <summary>
        /// 自动生成生产单据,直接10到15
        /// </summary>
        /// <returns></returns>
        private static string InsertBoarCollection(string collector, string deviceid,string boarid,string collectStation,string WorkstationNo,string doperson)
        {
            //增加一条逻辑，当耳标扫码有订单未处理时，先删除这张未处理订单（仅限自动生成的订单）
            string sql = "SELECT count(*) sl FROM dbo.boar_collection WHERE boarid ='" + boarid + "' AND IsStatus='10'";
            string SaleOrderID = "";
            DataSet dssod = null;
            XHD.BLL.Sale_order_details sod = new XHD.BLL.Sale_order_details();
            if (SqlHelper.Exists(sql))
            {
                sql = "Delete boar_collection WHERE boarid ='" + boarid + "' AND IsStatus='10'";
                SqlHelper.ExecteNonQueryText(sql);
                XHD.BLL.Product pro = new XHD.BLL.Product();
                XHD.BLL.Basic_boar bb = new XHD.BLL.Basic_boar();
                DataSet dsboar = bb.GetList(" id='" + boarid + "'");
                DataSet dspro = pro.GetList(" id='" + dsboar.Tables[0].Rows[0]["category_id"].ToString() + "'");
                string  product_id = dspro.Tables[0].Rows[0]["id"].ToString();
                dssod = sod.GetList(" product_id='"+ product_id + "'   ");
            }
            else {//如果存在就不需要再加入销售订单
              
                try
                {
                    XHD.Model.Sale_order m = new Sale_order(); XHD.Model.Sale_order_details md = new Sale_order_details();
                    m.Customer_id = "临时客户";
                    XHD.BLL.Sys_Param s = new XHD.BLL.Sys_Param();
                    DataSet dspaytype = s.GetList(" params_type='pay_type'");
                    if (dspaytype.Tables[0].Rows.Count > 0)
                        m.pay_type_id = dspaytype.Tables[0].Rows[0]["id"].ToString();
                    XHD.BLL.hr_employee hr = new XHD.BLL.hr_employee();
                    DataSet dshr = hr.GetList(" name='" + collector + "'");
                    if (dshr.Tables[0].Rows.Count > 0)
                    {
                        m.emp_id = dshr.Tables[0].Rows[0]["id"].ToString();
                        m.create_id = dshr.Tables[0].Rows[0]["id"].ToString();
                    }

                    DataSet dsorderstatus = s.GetList(" params_type='order_status'");
                    if (dsorderstatus.Tables[0].Rows.Count > 0)
                        m.Order_status_id = dsorderstatus.Tables[0].Rows[0]["id"].ToString();
                    XHD.BLL.Product pro = new XHD.BLL.Product();
                    XHD.BLL.Basic_boar bb = new XHD.BLL.Basic_boar();
                    DataSet dsboar = bb.GetList(" id='" + boarid + "'");
                    DataSet dspro = pro.GetList(" id='" + dsboar.Tables[0].Rows[0]["category_id"].ToString() + "'");
                    md.product_id = dspro.Tables[0].Rows[0]["id"].ToString();
                    md.agio = strtodec(dspro.Tables[0].Rows[0]["agio"].ToString());

                    //2024-3-14 修改自动生成订单，下面三个参数对应
                    md.quantity = strtoint(dspro.Tables[0].Rows[0]["Quantity"].ToString());
                    md.capacity = strtodec(dspro.Tables[0].Rows[0]["price"].ToString()); ;
                    md.uspd = strtoint(dspro.Tables[0].Rows[0]["EffectiveSperm"].ToString()); ;
                   
                    md.amount = strtodec(dspro.Tables[0].Rows[0]["agio"].ToString());
                    
                    SaleOrderID = SaveOrder(m, md);
                }
                catch { }
            }
            XHD.BLL.boar_collection order = new XHD.BLL.boar_collection();
            XHD.Model.boar_collection model = new XHD.Model.boar_collection();
        
            model.collector = collector;
            model.collectDate = DateTime.Now;
            model.deviceid = deviceid;
            try
            {
               if(dssod==null)
                  dssod = sod.GetList(" order_id='" + SaleOrderID + "'");
                model.orderid = dssod.Tables[0].Rows[0]["order_id"].ToString(); 
                model.ordermxid = dssod.Tables[0].Rows[0]["product_id"].ToString();
            }
            catch { }
            // model.para1 = boarid;
            model.boarid = boarid;

            //有可能是短号，barcode，必须关联
            
            model.collectStation = collectStation;
            model.WorkstationNo = WorkstationNo;
             
            model.remarks = "自动生成";
         
            string id = sp.GetBarCode("WORK");
            model.id = id;// sp.GetBarCode("WORK") ;
            model.dotime = DateTime.Now;
            model.doperson = doperson;
            model.IsStatus = "10";//保存为0
                                  // order.UpdateOrderStatus(orderid,ordermxid, "1");//去除一个订单明细只能取一头猪的限制，后续改为手工结案，更新订单状态
                                  //model.arrears_invoice = decimal.Parse(request["T_amount"]);
              bool r=  order.Add(model);
              string  EventType = "新增种禽流程";

            //一旦保存，在途
            //StringBuilder sb = new StringBuilder();
            //sb.AppendLine("   UPDATE B SET	B.status='N' ,B.CollectionStatus=1");//更新为在途状态(状态为不可用)
            //sb.AppendLine("  FROM dbo.boar_collection A ");
            //sb.AppendLine("  INNER JOIN  dbo.Basic_boar B ON  A.boarid = B.id");
            //sb.AppendLine(" WHERE A.id='" + id + "'  ");
            //SqlParameter[] parameters = { };
            //DbHelperSQL.ExecuteSql(sb.ToString(), parameters);
         

            string UserID = "";
            string UserName = doperson;
            string IPStreet = "";
            string EventTitle = id;
            string EventID = id;

            Syslog.Add_log(UserID, UserName, IPStreet, EventTitle, EventType, EventID, "自动生成单据"+ SaleOrderID);
            if (r)
                return id;
            else return "";
        }

        private static string SaveOrder(XHD.Model.Sale_order m, XHD.Model.Sale_order_details md)
        {

          XHD.BLL.Sale_order order = new XHD.BLL.Sale_order();
            XHD.Model.Sale_order model = new Sale_order();
           model.Customer_id = m.Customer_id; 
           model.Order_date = DateTime.Now;

            model.pay_type_id =m.pay_type_id;
            model.Order_details = m.Order_details;
            model.Order_status_id = m.Order_status_id;

            model.Order_amount = 0;
            model.discount_amount = 0;
            model.total_amount = 0;

            model.emp_id = m.emp_id;

           string id = Guid.NewGuid().ToString();
            model.id = id;
            model.create_id = m.emp_id;
            model.create_time = DateTime.Now;
            model.IsStatus = "Y";//默认状态

            model.arrears_money = model.Order_amount;
            model.receive_money = 0;
            model.arrears_invoice = model.Order_amount;
            model.invoice_money = 0;

            model.Serialnumber = sp.GetBarCode("ORDER");// SO-20181220001
                                                        //"DD-" + DateTime.Now.ToString("yyyy-MM-dd-") + DateTime.Now.GetHashCode().ToString().Replace("-", "");
                                                        //model.arrears_invoice = decimal.Parse(request["T_amount"]);
            Syslog.Add_log("", "", "", model.Serialnumber, "临时客户", "", "Sale_order");
            var mm = order.Add(model);
             
            var cod = new XHD.BLL.Sale_order_details();
            var modeldel = new  Sale_order_details()
            {
                order_id = id,
            }; 
            modeldel.product_id = md.product_id; 
            modeldel.quantity =md.quantity;
            modeldel.agio =md.agio;
            modeldel.amount = md.amount;
            modeldel.capacity = md.capacity;
            modeldel.detail_status = "1";//明细默认状态
            modeldel.uspd = md.uspd; ;
         
            var d =  cod.Add(modeldel);
            if (mm && d)
                return id;
            else
            {
                Syslog.Add_log("", "", "", model.Serialnumber, "临时客户", "", "Sale_order_details新增失败");

                return "error";
              
            }
        }
        public static Tuple<bool, string> Update_boar_collection_status(string type, string id)
        {
            string IsStatus = "";//作业单提交，默认
            // PageValidate.InputText(request["IsStatus"], 50);
            string status = "";
            var sb = new System.Text.StringBuilder();
            sb.AppendLine("UPDATE boar_collection");

            if (type == "density")//密度
            { IsStatus = "60"; status = "密度分析"; }
            if (type == "VIT")//活力
            { IsStatus = "80"; status = "活力分析"; }
            if (type == "dilution")//活力
            { IsStatus = "95"; status = "稀释分析"; }
            else if (type == "packing")//打包
            { IsStatus = "A0"; status = "流程打包"; }
            if (type == "In")//活力
            { IsStatus = "B0"; status = "入库"; }
            if (type == "Out")//活力
            { IsStatus = "YY"; status = "出库"; }
            if (IsStatus != "")
                sb.AppendLine(" SET  IsStatus = '" + IsStatus + "' ");
            sb.AppendLine("  WHERE id= '" + id + "' ");

            string andIsSatatus = "";
            if (type == "density")//密度
            { andIsSatatus = "'40','50'"; status = "密度分析"; }
            if (type == "VIT")//活力
            { andIsSatatus = "'20','30','40','50','60','70'"; status = "活力分析"; }
            if (type == "dilution")//活力
            { andIsSatatus = "'80','90'"; status = "稀释分析"; }
            else if (type == "packing")//打包
            { andIsSatatus = "'95','96'"; status = "流程打包"; }
            if (type == "In")//活力
            { andIsSatatus = "'A0'"; status = "入库"; }
            if (type == "Out")//活力
            { andIsSatatus = "'B0'"; status = "出库"; }

            if (andIsSatatus != "")
                sb.AppendLine(" AND IsStatus in(" + andIsSatatus + ") ");




            try
            {
                if (SqlHelper.ExecteNonQueryText(sb.ToString()) > 0)

                    Tuple.Create<bool, string>(true, string.Empty);

                else return Tuple.Create<bool, string>(false, "更新失败！");
            }
            catch (Exception exception)
            {
                return Tuple.Create<bool, string>(false, exception.Message);
            }
            finally
            {

            }
            return Tuple.Create<bool, string>(true, string.Empty);
        }

        public static Tuple<bool, string, DataTable> GetListboar_collection_condition(string barcode, string type)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine(" SELECT A.id,A.collectDate,A.IsStatus ,A.weights ,ISNULL(PrintCount,0) PrintCount  ");
            sb.AppendLine("              ,B.capacity,B.quantity ,lev.LevelName   ");
            sb.AppendLine("              ,C.Serialnumber,C.Order_date     ");
            sb.AppendLine("              ,D.barcode ,sas.breed  product_name ,D.shortcode    ");//20250913修改V SAS breedV SAS.usefulspermperdosis
            sb.AppendLine("              ,E.StatusName,A.collector   ");
            sb.AppendLine(" 			 ,hr.uid,DATEDIFF(DAY, D.birthday,GETDATE()) nlday ,D.product_name as pz    ");
            //if (type == "dilution" || type == "packing")//稀释-ISNULL(PrintCount,0)
            sb.AppendLine("  ,dd.* ");
            sb.AppendLine(" , sas.usefulspermperdosis, sas.breed ");///20250913修改V SAS breedV SAS.usefulspermperdosisBoar ISASpSUS. motpercen
            { sb.AppendLine(" 	  ,produceddosis as RealyQuantity    "); }
            sb.AppendLine("              FROM  dbo.boar_collection A     ");
            sb.AppendLine("              left JOIN  dbo.Sale_order_details B ON	A.ordermxid=B.product_id AND	 A.orderid=B.order_id     ");
            sb.AppendLine("              left JOIN  dbo.Sale_order C ON	A.orderid=C.id     ");
            sb.AppendLine("              left JOIN dbo.Basic_boar D ON	A.boarid=D.id     ");
            sb.AppendLine(" LEFT JOIN dbo.Product cat ON	 d.category_id=cat.id     ");
            sb.AppendLine(" LEFT JOIN dbo.Basic_Product_Series ser ON d.Pro_SeriesId=ser.id     ");
            sb.AppendLine(" LEFT JOIN  dbo.Basic_Product_Series_Level lev ON lev.id=d.Pro_Series_LevelId   "); 
            sb.AppendLine("              INNER JOIN  dbo.DocStatus_Name E ON	A.IsStatus=E.IsStatusCode     ");
            sb.AppendLine("             LEFT JOIN  dbo.hr_employee hr ON	A.collector=hr.name  "); 
            //if (type == "dilution" || type == "packing")//稀释
            {
                sb.AppendLine(" LEFT JOIN (  ");
                sb.AppendLine("   SELECT TOP	 1  CreateTime ct     ");
                sb.AppendLine("                  ,*     ");
                sb.AppendLine("                  FROM dbo.Boar_ISASpSUS ");
                sb.AppendLine(" WHERE collectionID= '"+ barcode + "'");
                sb.AppendLine(" ORDER BY CreateTime DESC	     ");
                sb.AppendLine("               ");
                sb.AppendLine(" )dd  ");
                sb.AppendLine(" ON	 A.id=dd.collectionID  ");
            }
            sb.AppendLine(" LEFT JOIN V_SAS sas on  A.id=sas.id  ");
            sb.AppendLine(" 			 WHERE	 A.IsStatus NOT in ('NN' ,'YY')    ");

            if (barcode != "")
                sb.AppendLine(" and A.ID='" + barcode + "'");


            string andIsSatatus = ""; string status = "";
            if (type == "density")//密度
            { andIsSatatus = "'40','50'"; status = "密度分析"; }
            if (type == "VIT")//活力
            { andIsSatatus = " '20','30','40','50','60','70'"; status = "活力分析"; }//'20','30','40','50','60','70'
            if (type == "dilution")//稀释
            { andIsSatatus = "'80','90'"; status = "稀释分析"; }
              if (type == "packing")//打包
            { andIsSatatus = "'95','96'"; status = "流程打包"; }
            if (type == "In")//活力
            { andIsSatatus = "'A0'"; status = "入库"; }
            if (type == "Out")//活力
            { andIsSatatus = "'B0'"; status = "出库"; }

            if (andIsSatatus != "")
                sb.AppendLine(" AND A.IsStatus in(" + andIsSatatus + ") ");


            DataTable lsds = new DataTable();
            try
            {
                lsds = SqlHelper.ExecuteDataTableText(sb.ToString());
            }
            catch (Exception exception)
            {
                return Tuple.Create<bool, string, DataTable>(false, exception.Message, lsds);
            }
            finally
            {

            }
            return Tuple.Create<bool, string, DataTable>(true, string.Empty, lsds);
        }

        public static Tuple<bool, string> Update_boar_collection_statusPacking(string id,int lsh,string IsPrint,string remarks)
        {
            StringBuilder sb = new StringBuilder(); 
                    //for (int i = 0; i<int.Parse(dilution_quantity); i++)
                    //{
                         string carcode = id + lsh.ToString("000");
            //sb.AppendLine(" DELETE boar_collection_dilution_barcode WHERE bc_id='" + id + "' AND barcode='"+ carcode + "' ");
            sb.AppendLine(" INSERT INTO dbo.boar_collection_dilution_barcode  ");
                        sb.AppendLine("         ( bc_id, barcode, dotime, remarks,IsPrint )  ");
                        sb.AppendLine(" VALUES  ( '"+ id + "', -- bc_id - varchar(50)  ");
                        sb.AppendLine("            '"+ carcode + "', -- barcode - varchar(50)  ");
                        sb.AppendLine("           GETDATE(), -- dotime - datetime  ");
                        sb.AppendLine("           '"+ remarks + "','"+IsPrint+"'  -- remarks - varchar(50)  ");
                        sb.AppendLine("           )   ");
            if(IsPrint=="Y")
            {
                sb.AppendLine(" UPDATE dbo.boar_collection SET	");
                //sb.AppendLine(" PrintCount =ISNULL(PrintCount,0)+1 ");
                sb.AppendLine(" PrintCount=");
                sb.AppendLine(" (SELECT COUNT(1) FROM (  ");
                sb.AppendLine(" SELECT DISTINCT barcode FROM    ");
                sb.AppendLine(" dbo.boar_collection_dilution_barcode  ");
                sb.AppendLine(" WHERE bc_id='" + id + "'  ");
                sb.AppendLine(" )AA)  ");
                sb.AppendLine("  WHERE id='"+id+"' ");
            }
            try
            {
                if (SqlHelper.ExecteNonQueryText(sb.ToString()) > 0)

                    Tuple.Create<bool, string>(true, string.Empty);

                else return Tuple.Create<bool, string>(false, "更新失败！");
            }
            catch (Exception exception)
            {
                return Tuple.Create<bool, string>(false, exception.Message);
            }
            finally
            {

            }
            return Tuple.Create<bool, string>(true, string.Empty);
        }
        /// <summary>
        /// IsStatus -A0-_-10-
        /// </summary>
        /// <param name="IsStatus"></param>
        /// <returns></returns>
        public static Tuple<bool, string, DataSet> GetCollection_ByConditon(string IsStatus)
        {
            StringBuilder sb1 = new StringBuilder();
            sb1.AppendLine(" SELECT A.*,b.barcode,b.shortcode,b.product_name FROM dbo.boar_collection a LEFT JOIN dbo.Basic_boar b ON a.boarid=b.id ");
            sb1.AppendLine(" WHERE IsStatus in( "+IsStatus.Replace('-','\'').Replace('_',',')+") AND collectDate<=GETDATE() ORDER BY a.id ");
        
            DataSet lsds = null;
            try
            {
                lsds = SqlHelper.ExecuteDataSetText(sb1.ToString()  );
            }
            catch (Exception exception)
            {
                return Tuple.Create<bool, string, DataSet>(false, exception.Message, lsds);
            }
            finally
            {

            }
            return Tuple.Create<bool, string, DataSet>(true, string.Empty, lsds);
        }

        public static Tuple<bool, string, DataSet> GetCollection_all_data()
        {
            //StringBu】ilder sb1 = new StringBuilder();
            //sb1.AppendLine(" SELECT b.barcode,b.shortcode FROM dbo.boar_collection a LEFT JOIN dbo.Basic_boar b ON a.boarid=b.id WHERE IsStatus='10' AND collectDate<=GETDATE() ORDER BY a.id ");
            StringBuilder sb = new StringBuilder();
            sb.AppendLine(" SELECT d.corralName,b.product_name cname,a.shortcode,CASE WHEN c.id IS NOT NULL THEN '#ff7575' END color FROM Basic_boar a  ");
            sb.AppendLine(" LEFT JOIN Product b ON a.category_id=b.id  ");
            sb.AppendLine(" LEFT JOIN boar_collection c ON c.boarid=a.id AND c.IsStatus='10'  ");
            sb.AppendLine(" LEFT JOIN Basic_corral d ON d.id=a.corralId  ");
            sb.AppendLine(" WHERE a.LastCollecTime+ISNULL(b.pickcycle,0)<=GETDATE()  OR a.LastCollecTime IS NULL ");
            sb.AppendLine(" ORDER BY CASE WHEN c.id IS NOT NULL THEN '#ff7575' END DESC,a.LastCollecTime  ");

            StringBuilder sb2 = new StringBuilder();
            sb2.AppendLine(" SELECT b.product_name AS cname,COUNT(*)sl  ");
            sb2.AppendLine("  FROM Basic_boar a  ");
            sb2.AppendLine(" LEFT JOIN Product b ON a.category_id=b.id  ");
            sb2.AppendLine(" WHERE a.LastCollecTime>=CONVERT(VARCHAR(10), GETDATE(), 120)  ");
            sb2.AppendLine(" GROUP BY b.product_name  ");

            DataSet lsds = null;
            try
            {//sb1.ToString()+"  "+
                lsds = SqlHelper.ExecuteDataSetText(sb.ToString()+" "+sb2.ToString());
            }
            catch (Exception exception)
            {
                return Tuple.Create<bool, string, DataSet>(false, exception.Message, lsds);
            }
            finally
            {

            }
            return Tuple.Create<bool, string, DataSet>(true, string.Empty, lsds);
        }

        public static Tuple<bool, string, DataTable> GetSys_info()
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine(" SELECT * FROM dbo.Sys_info");
            DataTable lsds = null;
            try
            {
                lsds = SqlHelper.ExecuteDataTableText(sb.ToString());
            }
            catch (Exception exception)
            {
                return Tuple.Create<bool, string, DataTable>(false, exception.Message, lsds);
            }
            finally
            {

            }
            return Tuple.Create<bool, string, DataTable>(true, string.Empty, lsds);
        }


        public static int ExecUpdateStatusJob()
        {

            string sql= "UPDATE a SET a.status='Y',a.CollectionStatus='1',a.EfficacyState='1' "+
             " FROM dbo.Basic_boar a "+
            " LEFT JOIN Product b ON a.category_id = b.id "+
            " WHERE(a.status = 'N' OR a.CollectionStatus = '3' OR a.EfficacyState = '3') AND LastCollecTime+b.pickcycle < GETDATE()";
           int i=  DbHelperSQL.ExecuteSql(sql);
            return i;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="weights"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public static Tuple<bool, string> Insert_boar_ISASpSUS(Boar_ISASpSUS bi)
        {
          //  int totalfinalvolume = bi.totalfinalvolume==null ? 0 : bi.totalfinalvolume;

           StringBuilder sb = new StringBuilder();
            sb.AppendLine(" DELETE Boar_ISASpSUS WHERE  collectionID='" + bi.collectionID + "'");
            sb.AppendLine(" INSERT INTO dbo.Boar_ISASpSUS ");
            sb.AppendLine("         ( collectionID ,  ");
            sb.AppendLine("           casereference ,  ");
            sb.AppendLine("           collector ,  ");
            sb.AppendLine("           c_operator ,  ");
            sb.AppendLine("           dosisvolume ,  ");
            sb.AppendLine("           c_subject ,  ");
            sb.AppendLine("           ejaculatedvolume ,  ");
            sb.AppendLine("           produceddosis ,  ");
            sb.AppendLine("           diluentvolume ,  ");
            sb.AppendLine("           totalfinalvolume ,  ");
            sb.AppendLine("           CreateTime,totalspermsperdosis,dilutionratio,usemml  ");
            sb.AppendLine("   ,totnumber");
            sb.AppendLine("    ,totpercen ,  ");
            sb.AppendLine("           totmillions ,  ");
            sb.AppendLine("           totmml ,  ");
            sb.AppendLine("           nornumber ,  ");
            sb.AppendLine("           norpercen ,  ");
            sb.AppendLine("           normillions ,  ");
            sb.AppendLine("           normml ,  ");
            sb.AppendLine("           motnumber ,  ");
            sb.AppendLine("           motpercen ,  ");
            sb.AppendLine("           motmillions ,  ");
            sb.AppendLine("           motmml ,  ");
            sb.AppendLine("           mnonumber ,  ");
            sb.AppendLine("           mnopercen ,  ");
            sb.AppendLine("           mnomillions ,  ");
            sb.AppendLine("           mnomml ,  ");
            sb.AppendLine("           usenumber ,  ");
            sb.AppendLine("           usepercen ,  ");
            sb.AppendLine("           usemillions,  ");
            sb.AppendLine("  freshreference,  ");
            sb.AppendLine("     subject,  ");
            sb.AppendLine("     operator,  ");
            sb.AppendLine("     initialdilutorvolume,  ");
            sb.AppendLine("     analysisdilution,  ");
            sb.AppendLine("     usefulspermperdosis  ");

            sb.AppendLine("         )  ");
            sb.AppendLine(" VALUES  ( '"+bi.collectionID+"' , -- collectionID - varchar(50)  ");
            sb.AppendLine("           '" + bi.casereference + "' , -- casereference - varchar(50)  ");
            sb.AppendLine("           '" + bi.collector + "' , -- collector - varchar(20)  ");
            sb.AppendLine("          '" + bi.c_operator + "' , -- c_operator - varchar(20)  ");
            sb.AppendLine("            '" + bi.dosisvolume + "' , -- dosisvolume - int  ");
            sb.AppendLine("           '" + bi.c_subject + "' , -- c_subject - varchar(50)  ");
            sb.AppendLine("            '" +  bi.ejaculatedvolume + " ' , -- ejaculatedvolume - int  ");
            sb.AppendLine("           ' " + bi.produceddosis + " ' , -- produceddosis - int  ");
            sb.AppendLine("          '" + bi.diluentvolume + "' , -- diluentvolume - int  ");
            sb.AppendLine("            '" + bi.totalfinalvolume + " ' , -- totalfinalvolume - int  ");
            sb.AppendLine("           GETDATE(),   ");
            sb.AppendLine("         '"+bi.Totalspermsperdosis+"',  ");
            sb.AppendLine("          '" + bi.Dilutionratio + " ',   ");
            sb.AppendLine("          '" +strtodec( bi.usemml.ToString())+ "'  ");
            sb.AppendLine("         ,'" + bi.totnumber + "'  ");
            sb.AppendLine("      ,'" + bi.totpercen + "',  ");
            sb.AppendLine("           ' " + bi.totmillions + "',  ");
            sb.AppendLine("            '" + bi.totmml + "',  ");
            sb.AppendLine("           '" + bi.nornumber + "',  ");
            sb.AppendLine("            '" + bi.norpercen + "',  ");
            sb.AppendLine("           '" + bi.normillions + "' ,  ");
            sb.AppendLine("           '" + bi.normml + "',  ");
            sb.AppendLine("           '" + bi.motnumber + "',  ");
            sb.AppendLine("            '" + bi.motpercen + "',  ");
            sb.AppendLine("            '" + bi.motmillions + "',  ");
            sb.AppendLine("           '" + bi.motmml + "',  ");
            sb.AppendLine("           '" + bi.mnonumber + "',  ");
            sb.AppendLine("            '" + bi.mnopercen + "',  ");
            sb.AppendLine("           '" + bi.mnomillions + "',  ");
            sb.AppendLine("           '" + bi.mnomml + "',  ");
            sb.AppendLine("           '" + bi.usenumber + "',  ");
            sb.AppendLine("           '" + bi.usepercen + "',  ");
            sb.AppendLine("           '" + bi.usemillions + "',");
            sb.AppendLine("       '" + bi.freshreference + "', ");
            sb.AppendLine("       '" + bi.subject + "',  ");
            sb.AppendLine("       '" + bi.Operator+"',  ");
            sb.AppendLine("       '" + bi.initialdilutorvolume + " ',  ");
            sb.AppendLine("       '" + bi.analysisdilution + " ',  ");
            sb.AppendLine("       '" + bi.usefulspermperdosis + " '  ");

            sb.AppendLine(" )");

            try
            {
                if (SqlHelper.ExecteNonQueryText(sb.ToString()) > 0)
                    return Tuple.Create<bool, string>(true, string.Empty);
                else return Tuple.Create<bool, string>(false, "更新失败！");

            }
            catch (Exception exception)
            {
                return Tuple.Create<bool, string>(false, exception.Message);
            }
            finally
            {

            }
            return Tuple.Create<bool, string>(true, string.Empty);
        }

        public static Tuple<bool,string,DataTable> GetISASpSUS(string id) {


                  
            try
            {   
                string sql = "SELECT * FROM [V_SAS]  where id='" + id + "'";
                DataTable lsdt = SqlHelper.ExecuteDataTableText(sql,null);
                if (lsdt == null)
                {
                    return Tuple.Create<bool, string, DataTable>(false, "无数据", null);
                }
                else
                {
                    if(lsdt.Rows[0]["IsStatus"].ToString()=="20")
                    return Tuple.Create<bool, string, DataTable>(true, "success", lsdt);
                    else return Tuple.Create<bool, string, DataTable>(false, "此单据不在可分析状态!", lsdt);
                }

            }
            catch (Exception exception)
            {
                return Tuple.Create<bool,string, DataTable>(false,exception.Message, null);
            }
            finally
            {

            }
           
        }

        public static Tuple<bool, string> Insert_test(string test) 
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine(" INSERT INTO dbo.test  ");
            sb.AppendLine("         ( testvalue ,  "); 
            sb.AppendLine("           CreateTime  ");
            sb.AppendLine("         )  ");
            sb.AppendLine(" VALUES  ( '" + test + "' , ");
            sb.AppendLine("           GETDATE()  -- CreateTime - datetime  ");
            sb.AppendLine("         )  ");

            try
            {
                if (SqlHelper.ExecteNonQueryText(sb.ToString()) > 0)
                    return Tuple.Create<bool, string>(true, string.Empty);
                else return Tuple.Create<bool, string>(false, "更新失败！");

            }
            catch (Exception exception)
            {
                return Tuple.Create<bool, string>(false, exception.Message);
            }
            finally
            {

            }
            return Tuple.Create<bool, string>(true, string.Empty);
        }

        //时间戳 24小时 时分秒毫秒
        private static int timestamp()
        {
            DateTime dt = DateTime.Now;
            return dt.Hour * 1000 * 60 * 60 + dt.Minute * 1000 * 60 + dt.Second * 1000 + dt.Millisecond;

        }
        //转换为16进制 4个字节
        private static string GetTenTO4hex(int ten)
        {
            string S = ten.ToString("X"); //转16进制
            string TEST = ten.ToString("X");
            if (S.Length == 1)
                S = "0" + S + " 00 00 00";
            else if (S.Length == 2)
                S = S + " 00" + " 00 00";
            else if (S.Length == 3)
                S = S.Substring(1, 2) + " 0" + S.Substring(0, 1) + " 00 00";
            else if (S.Length == 4)
                S = S.Substring(2, 2) + " " + S.Substring(0, 2) + " 00 00";
            else if (S.Length == 5)
                S = S.Substring(3, 2) + " " + S.Substring(1, 2) + " 0" + S.Substring(0, 1) + " 00";
            else if (S.Length == 6)
                S = S.Substring(4, 2) + " " + S.Substring(2, 2) + " " + S.Substring(0, 2) + " 00";
            else if (S.Length == 7)
                S = S.Substring(5, 2) + " " + S.Substring(3, 2) + " " + S.Substring(1, 2) + " 0" + S.Substring(0, 1);
            else
                S = S.Substring(6, 2) + " " + S.Substring(4, 2) + " " + S.Substring(2, 2) + " " + S.Substring(0, 2);

            return S;
        }

        //低位优先
        public string GetTenTO2hex(int ten)
        {
            string S = ten.ToString("X"); //转16进制
            if (S.Length == 1)
                S = "0" + S + " 00";
            else if (S.Length == 2)
                S = S + " 00";
            else if (S.Length == 3)
                S = S.Substring(1, 2) + " 0" + S.Substring(0, 1);
            else
                S = S.Substring(2, 2) + " " + S.Substring(0, 2);

            return S;
        }

        /// <summary>
        /// 获取本地IP地址信息
        /// </summary>
      static  string  GetAddressIP()
        {
            ///获取本地的IP地址
            string AddressIP = string.Empty;
            foreach (IPAddress _IPAddress in Dns.GetHostEntry(Dns.GetHostName()).AddressList)
            {
                if (_IPAddress.AddressFamily.ToString() == "InterNetwork")
                {
                    AddressIP = _IPAddress.ToString();
                }
            }
           return   AddressIP;
        }
        public static Tuple<bool, string> exist_SAS_InfoData(SAS_InfoDataEntity entity)
        {
            try
            {
                XHD.BLL.SAS_InfoDataBll BLL = new XHD.BLL.SAS_InfoDataBll();
                var r = BLL.Exists(entity.casereference);
                if (r)
                    return Tuple.Create<bool, string>(true, string.Empty);
                else return Tuple.Create<bool, string>(false, "不存在！");

            }
            catch (Exception exception)
            {
                return Tuple.Create<bool, string>(false, exception.Message);
            }
            finally
            {

            }
            return Tuple.Create<bool, string>(true, string.Empty);

        }


        public static Tuple<bool,string, SAS_InfoDataEntity> Get_SAS_InfoData(string id)
        {
            try
            {
                XHD.BLL.SAS_InfoDataBll BLL = new XHD.BLL.SAS_InfoDataBll();
               List<SAS_InfoDataEntity> dd = BLL.GetModelList(" casereference='"+id+"'");
                if (dd.Count()>0)
                    return Tuple.Create<bool,string, SAS_InfoDataEntity>(true,string.Empty,dd.FirstOrDefault());
                else return Tuple.Create<bool, string, SAS_InfoDataEntity>(false, "不存在",null);

            }
            catch (Exception exception)
            {
                return Tuple.Create<bool, string, SAS_InfoDataEntity>(false, exception.Message,null);
            }
            finally
            {

            }
          

        }

        public static Tuple<bool, string> Insert_SAS_InfoData(SAS_InfoDataEntity entity,List<SAS_InfoImgDataEntity> imgEntity)
        {
            

            try
            {
                XHD.BLL.SAS_InfoDataBll BLL = new XHD.BLL.SAS_InfoDataBll(); 
                var x = BLL.Exists(entity.casereference);
                bool InsertData = false,InsertImage;
                if (!x)
                {
                    InsertData = BLL.Insert(entity);
                    InsertImage = InserImgData(imgEntity);
                   

                }
                else
                {
                    InsertData = BLL.Update(entity);
                    InsertImage= InserImgData(imgEntity);
                  

                }
               
                if (InsertData && InsertImage) {
                    Boar_ISASpSUS et = SmartDataTableExtensions.ConvertModel<Boar_ISASpSUS, SAS_InfoDataEntity>(entity);
        
                    
                    et.collectionID = entity.casereference; 
                    et.CreateTime = DateTime.Now;
                    et.c_operator = entity.Operator; 
                    et.c_subject = entity.subject;  
                    var rx = Insert_boar_ISASpSUS(et);
                    string sql = "UPDATE dbo.boar_collection SET	IsStatus=80 WHERE id='" + entity.casereference + "' AND	 IsStatus IN('20','30','40','50','60','70')";
                    int i = DbHelperSQL.ExecuteSql(sql);
                    if (rx.Item1)
                    {
                        if (i > 0)
                            return Tuple.Create<bool, string>(true, string.Empty);
                        else return Tuple.Create<bool, string>(false, entity.casereference + "单据状态更新失败!");
                    }
                    
                    else return Tuple.Create<bool, string>(false, rx.Item2);
                } 
                     
            }
            catch (Exception exception)
            {
                return Tuple.Create<bool, string>(false, exception.Message);
            }
            finally
            {

            }
            return Tuple.Create<bool, string>(true, string.Empty);
        }

        public static Tuple<bool, string> Insert_SAS_InfoData_ForMF(SAS_InfoDataEntity entity, List<SAS_InfoImgDataEntity> imgEntity)
        {


            try
            {
                XHD.BLL.SAS_InfoDataBll BLL = new XHD.BLL.SAS_InfoDataBll();
                var x = BLL.Exists(entity.casereference);
                bool InsertData = false, InsertImage;
                if (!x)
                {
                    InsertData = BLL.Insert(entity);
                    InsertImage = InserImgData(imgEntity);


                }
                else
                {
                    InsertData = BLL.Update(entity);
                    InsertImage = InserImgData(imgEntity);


                }

                if (InsertData && InsertImage)
                {
                //    Boar_ISASpSUS et = new Boar_ISASpSUS();
                //    et.collectionID = entity.casereference;
                //    et.casereference = entity.casereference;
                //    et.collector = entity.collector;
                //    et.CreateTime = DateTime.Now;
                //    et.c_operator = entity.Operator;
                //    et.Norpercen = strtodec(entity.norpercen.ToString());
                //    et.produceddosis = entity.produceddosis;
                //    et.Subject = entity.subject;
                //    et.Analysisdilution = entity.analysisdilution;
                //    et.c_subject = entity.subject;
                //    et.diluentvolume = entity.diluentvolume;
                //    et.Dilutionratio = entity.dilutionratio;
                //    et.dosisvolume = entity.dosisvolume;
                //    et.ejaculatedvolume = entity.ejaculatedvolume;
                //    //et.Freshreference=entity.
                //    et.Initialdilutorvolume = entity.initialdilutorvolume;
                //    et.Mnomillions = entity.mnomillions;
                //    et.Mnomml = strtodec(entity.mnomml.ToString());
                //    et.Mnonumber = entity.mnonumber;
                //    et.Mnopercen = strtodec(entity.mnopercen.ToString());
                //    et.Motmillions = entity.motmillions;
                //    et.Motmml = strtodec(entity.motmml.ToString());
                //    et.Motnumber = strtoint(entity.motnumber.ToString());
                //    et.Motpercen = strtodec(entity.motpercen.ToString());
                //    et.Normillions = entity.normillions;
                //    et.Normml = entity.normml;
                //    et.Norpercen = strtodec(entity.norpercen.ToString());
                //    et.Operator = entity.Operator;
                //    et.produceddosis = entity.produceddosis;
                //    et.totalfinalvolume = entity.totalfinalvolume;
                //    et.Totalspermsperdosis = entity.totalspermsperdosis;
                //    et.Totmillions = entity.totmillions;
                //    et.Totmml = strtodec(entity.totmml.ToString());
                //    et.Totnumber = entity.totnumber;
                //    et.Totpercen = strtodec(entity.totpercen.ToString());
                //    et.Usefulspermperdosis = entity.usefulspermperdosis;
                //    et.Usemillions = entity.usemillions;
                //    et.Usemml = strtodec(entity.usemml.ToString());
                //    et.Usenumber = strtoint(entity.usenumber.ToString());
                //    et.Usepercen = strtodec(entity.usepercen.ToString());

                //    var rx = Insert_boar_ISASpSUS(et);
                //    string sql = "UPDATE dbo.boar_collection SET	IsStatus=80 WHERE id='" + entity.casereference + "' AND	 IsStatus IN('20','30','40','50','60','70')";
                //    int i = DbHelperSQL.ExecuteSql(sql);
                //    if (rx.Item1)
                //    {
                       //if (i>0)
                     return Tuple.Create<bool, string>(true, string.Empty);
                    // }

                    //else return Tuple.Create<bool, string>(false, rx.Item2);
                }
                else return Tuple.Create<bool, string>(false, entity.casereference + "保存数据失败，请检查数据格式和类型!");


            }
            catch (Exception exception)
            {
                return Tuple.Create<bool, string>(false, exception.Message);
            }
            finally
            {

            }
            return Tuple.Create<bool, string>(true, string.Empty);
        }

        private static bool InserImgData(List<SAS_InfoImgDataEntity> imgEntity)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine(" DELETE SAS_InfoImgData WHERE casereference='" + imgEntity.FirstOrDefault().casereference + "'    ");
            foreach (SAS_InfoImgDataEntity entity in imgEntity)
            {
               
                sb.AppendLine(" insert into SAS_InfoImgData(casereference,img_Name,orgin_Name,createtime)  ");
                sb.AppendLine(" values ('" + entity.casereference+"','"+entity.img_Name+"','"+entity.orgin_Name+"','"+entity.createtime+"')  ");
                sb.AppendLine("   ");

            }
         int i=   DbHelperSQL.ExecuteSql(sb.ToString());
            return i > 0 ? true : false;

        }

        public static bool ExistDoc(string    id)
        {

            string strSql = "SELECT count(1) FROM dbo.boar_collection  WHERE id = '" + id + "' AND IsStatus IN('20')";
            return DbHelperSQL.Exists(strSql.ToString());
        }


        private static decimal strtodec(string s)
        {
            decimal d = 0;
            try {
                d = decimal.Parse(s);
            }
            catch { }

            return d;
        }

        private static int strtoint(string s)
        {
            int d = 0;
            try
            {
                d = int.Parse(s);
            }
            catch { }

            return d;
        }
        private static double strtodouble(string s)
        {
            double d = 0;
            try
            {
                d = double.Parse(s);
            }
            catch { }

            return d;
        }
    }
}
